package com.hh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hh.jdbc.JDBCDemo;

public class SubjectDAO {
	
	public JSONObject getUser(String zyhm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONObject object = new JSONObject();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select hzid,hyxm,hyxb,zych,bqmc from VIEW_ZY_BRJBXX_HIS"
            		+ " where zyhm='"+zyhm+"'";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            if(res.next()){
            	object.put("hzid", res.getString("hzid"));
            	object.put("hyxm", res.getString("hyxm"));
            	if(res.getInt("hyxb")==1){
            		object.put("hyxb","男" );
            	}else{
            		object.put("hyxb","女" );
            	}
            	object.put("zych", res.getString("zych"));
            	object.put("bqmc", res.getString("bqmc"));
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return object;
	}
	
	
	public JSONObject getAssess(String pdbbm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONObject object = new JSONObject();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from PD_PDB1 where pdbbm='"+pdbbm+"'";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            if(res.next()){
            	object.put("pdbmc", res.getString("PDBMC"));
            	object.put("pdbbm", res.getString("PDBBM"));
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return object;
	}
	
	public JSONArray getAssesses(){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from PD_PDB1 ";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("pdbmc", res.getString("PDBMC"));
            	object.put("pdbbm", res.getString("PDBBM"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	
	public JSONArray getAssessesByZyhm(String zyhm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select pdbm,hyxm,pdbmc,pdbbm,pdrq,pdry,pdfs,pdjg "
            		+ "from VIEW_ZY_BRJBXX_HIS,PD_RYPDB1 "
            		+ "where VIEW_ZY_BRJBXX_HIS.ZYHM=PD_RYPDB1.zyhm and TJBZ=1 and PD_RYPDB1.zyhm='"+zyhm+"'";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("pdbm", res.getString("pdbm"));
            	object.put("pdbbm", res.getString("pdbbm"));
            	object.put("hyxm", res.getString("hyxm"));
            	object.put("pdbmc", res.getString("pdbmc"));
            	object.put("pdrq", res.getString("pdrq"));
            	object.put("pdry", res.getString("pdry"));
            	object.put("pdfs", res.getString("pdfs"));
            	object.put("pdjg", res.getString("pdjg"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	
	public JSONArray getSubjects(String pdbbm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from pd_yszb where pdbbm='"+pdbbm +"' order by xssxh ";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("ysbm", res.getString("YSBM"));
            	object.put("ysmc", res.getString("YSMC"));
            	object.put("pdbbm", res.getString("PDBBM"));
            	object.put("xssxh", res.getString("XSSXH"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	
	
	public JSONArray getChooses(String ysbm,String yszbm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from PD_PDB3 where ysbm='"+ysbm +"' order by YSZF desc";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("pdbbm", res.getString("PDBBM"));
            	object.put("ysbm", res.getString("YSBM"));
            	object.put("yszmc", res.getString("YSZMC"));
            	object.put("yszbm", res.getString("YSZBM"));
            	object.put("yszf", res.getString("YSZF"));
            	if(yszbm!=null&&yszbm.equals(res.getString("YSZBM"))){
            		object.put("ischeck", 1);
            	}
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	
	
	public boolean insertAssess(String pdbm,String pdbbm,String pdbmc,String zyhm,Double pdfs,String ygdm,String pdjg){
		Connection con = null;
        PreparedStatement statement = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "insert into PD_RYPDB1 (PDBM,PDBBM,PDBMC,ZYHM,PDRQ,PDFS,TJBZ,PDRY,PDJG) values(?,?,?,?,GETDATE(),?,1,?,?)" ;
            statement = con.prepareStatement(sql);
            statement.setString(1, pdbm);
            statement.setString(2, pdbbm);
            statement.setString(3, pdbmc);
            statement.setString(4, zyhm);
            statement.setDouble(5, pdfs);
            statement.setString(6, ygdm);
            statement.setString(7, pdjg);
            int i  = statement.executeUpdate();
            if(i>0){
            	return true;
            }
           
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return false;
	}
	
	public boolean insertSubject(String pdbm,String ysbm,String ysmc,String yszbm,String yszmc){
		Connection con = null;
        PreparedStatement statement = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "insert into PD_RYPDB2 (PDBM,YSBM,YSMC,YSZBM,YSZMC) values(?,?,?,?,?)" ;
            statement = con.prepareStatement(sql);
            statement.setString(1, pdbm);
            statement.setString(2, ysbm);
            statement.setString(3, ysmc);
            statement.setString(4, yszbm);
            statement.setString(5, yszmc);
            int i  = statement.executeUpdate();
            if(i>0){
            	return true;
            }
           
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return false;
	}
	
	public Map<String,String> getChoosesByPdbm(String pdbm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        Map<String,String> map = new HashMap<String,String>();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from PD_RYPDB2 where PDBM='"+pdbm +"'";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	map.put(res.getString("YSBM"), res.getString("YSZBM"));
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return map;
		
	}
	

	public JSONArray getRules(String pdbbm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "select * from PD_PDB_GZ where pdbbm='"+pdbbm +"'";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("fh", res.getInt("fh"));
            	object.put("fs", res.getInt("fs"));
            	object.put("fh1", res.getInt("fh1"));
            	object.put("fs1", res.getInt("fs1"));
            	object.put("lj", res.getInt("lj"));
            	object.put("jg", res.getString("jg"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
}
